/************************************************************************************/
/*  Program name CAPPOEXT_REQUSITION_LINE.sql                                              */
/*  Creater:  Kenny, Leung (PCCW - UniHub)                                         */
/*************   History   *************************                                */
/* 16 Jan 2006, Kenny Leung         First created                               */
/************************************************************************************/
set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;s
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPPOEXT_REQUISITION_LINE';

	lv_Scc_Count	NUMBER	:=	0;
	lv_Err_Count	NUMBER	:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	lv_FileName		VARCHAR2(100)	:=	'CAPPO_REQUISITION_LINE.dat';
	lv_PathName		VARCHAR2(100)	:=	'/tmp';
	lv_UTL_File		UTL_FILE.File_Type;

	lv_NGO_Name		VARCHAR2(100);
	lv_NGO_Count	NUMBER;

	CURSOR Cur_F(p_NGO VARCHAR2) IS
	SELECT
		PHA.*
	FROM
		PO_REQUISITION_LINES_ALL		PHA,
		HR_ALL_ORGANIZATION_UNITS	HRO
	WHERE	PHA.ORG_ID	=	HRO.ORGANIZATION_ID
	AND		SUBSTR(HRO.NAME,4,4)	=	p_NGO;

BEGIN
	lv_NGO_Name		:=	SUBSTR(UPPER('&1'), -4);

	SELECT	COUNT('A')
	INTO	lv_NGO_Count
	FROM	HR_ALL_ORGANIZATION_UNITS
	WHERE	SUBSTR(Name,4,4)	=	lv_NGO_Name;

	IF	lv_NGO_Count	>=	1	THEN
        
        lv_UTL_File		:=	UTL_FILE.FOpen(lv_PathName, lv_FileName, 'w');
        
		FOR C1 IN Cur_F(lv_NGO_Name) LOOP
			
			UTL_FILE.PUT_LINE(
                  lv_UTL_File,
                  C1.REQUISITION_LINE_ID            ||CHR(9)||
                  C1.REQUISITION_HEADER_ID          ||CHR(9)||
                  C1.LINE_NUM                       ||CHR(9)||
                  C1.LINE_TYPE_ID                   ||CHR(9)||
                  C1.CATEGORY_ID                    ||CHR(9)||
                  C1.ITEM_DESCRIPTION               ||CHR(9)||
                  C1.UNIT_MEAS_LOOKUP_CODE          ||CHR(9)||
                  C1.UNIT_PRICE                     ||CHR(9)||
                  C1.QUANTITY                       ||CHR(9)||
                  C1.DELIVER_TO_LOCATION_ID         ||CHR(9)||
                  C1.TO_PERSON_ID                   ||CHR(9)||
                  C1.LAST_UPDATE_DATE               ||CHR(9)||
                  C1.LAST_UPDATED_BY                ||CHR(9)||
                  C1.SOURCE_TYPE_CODE               ||CHR(9)||
                  C1.LAST_UPDATE_LOGIN              ||CHR(9)||
                  C1.CREATION_DATE                  ||CHR(9)||
                  C1.CREATED_BY                     ||CHR(9)||
                  C1.ITEM_ID                        ||CHR(9)||
                  C1.ITEM_REVISION                  ||CHR(9)||
                  C1.QUANTITY_DELIVERED             ||CHR(9)||
                  C1.SUGGESTED_BUYER_ID             ||CHR(9)||
                  C1.ENCUMBERED_FLAG                ||CHR(9)||
                  C1.RFQ_REQUIRED_FLAG              ||CHR(9)||
                  C1.NEED_BY_DATE                   ||CHR(9)||
                  C1.LINE_LOCATION_ID               ||CHR(9)||
                  C1.MODIFIED_BY_AGENT_FLAG         ||CHR(9)||
                  C1.PARENT_REQ_LINE_ID             ||CHR(9)||
                  C1.JUSTIFICATION                  ||CHR(9)||
                  C1.NOTE_TO_AGENT                  ||CHR(9)||
                  C1.NOTE_TO_RECEIVER               ||CHR(9)||
                  C1.PURCHASING_AGENT_ID            ||CHR(9)||
                  C1.DOCUMENT_TYPE_CODE             ||CHR(9)||
                  C1.BLANKET_PO_HEADER_ID           ||CHR(9)||
                  C1.BLANKET_PO_LINE_NUM            ||CHR(9)||
                  C1.CURRENCY_CODE                  ||CHR(9)||
                  C1.RATE_TYPE                      ||CHR(9)||
                  C1.RATE_DATE                      ||CHR(9)||
                  C1.RATE                           ||CHR(9)||
                  C1.CURRENCY_UNIT_PRICE            ||CHR(9)||
                  C1.SUGGESTED_VENDOR_NAME          ||CHR(9)||
                  C1.SUGGESTED_VENDOR_LOCATION      ||CHR(9)||
                  C1.SUGGESTED_VENDOR_CONTACT       ||CHR(9)||
                  C1.SUGGESTED_VENDOR_PHONE         ||CHR(9)||
                  C1.SUGGESTED_VENDOR_PRODUCT_CODE  ||CHR(9)||
                  C1.UN_NUMBER_ID                   ||CHR(9)||
                  C1.HAZARD_CLASS_ID                ||CHR(9)||
                  C1.MUST_USE_SUGG_VENDOR_FLAG      ||CHR(9)||
                  C1.REFERENCE_NUM                  ||CHR(9)||
                  C1.ON_RFQ_FLAG                    ||CHR(9)||
                  C1.URGENT_FLAG                    ||CHR(9)||
                  C1.CANCEL_FLAG                    ||CHR(9)||
                  C1.SOURCE_ORGANIZATION_ID         ||CHR(9)||
                  C1.SOURCE_SUBINVENTORY            ||CHR(9)||
                  C1.DESTINATION_TYPE_CODE          ||CHR(9)||
                  C1.DESTINATION_ORGANIZATION_ID    ||CHR(9)||
                  C1.DESTINATION_SUBINVENTORY       ||CHR(9)||
                  C1.QUANTITY_CANCELLED             ||CHR(9)||
                  C1.CANCEL_DATE                    ||CHR(9)||
                  C1.CANCEL_REASON                  ||CHR(9)||
                  C1.CLOSED_CODE                    ||CHR(9)||
                  C1.AGENT_RETURN_NOTE              ||CHR(9)||
                  C1.CHANGED_AFTER_RESEARCH_FLAG    ||CHR(9)||
                  C1.VENDOR_ID                      ||CHR(9)||
                  C1.VENDOR_SITE_ID                 ||CHR(9)||
                  C1.VENDOR_CONTACT_ID              ||CHR(9)||
                  C1.RESEARCH_AGENT_ID              ||CHR(9)||
                  C1.ON_LINE_FLAG                   ||CHR(9)||
                  C1.WIP_ENTITY_ID                  ||CHR(9)||
                  C1.WIP_LINE_ID                    ||CHR(9)||
                  C1.WIP_REPETITIVE_SCHEDULE_ID     ||CHR(9)||
                  C1.WIP_OPERATION_SEQ_NUM          ||CHR(9)||
                  C1.WIP_RESOURCE_SEQ_NUM           ||CHR(9)||
                  C1.ATTRIBUTE_CATEGORY             ||CHR(9)||
                  C1.DESTINATION_CONTEXT            ||CHR(9)||
                  C1.INVENTORY_SOURCE_CONTEXT       ||CHR(9)||
                  C1.VENDOR_SOURCE_CONTEXT          ||CHR(9)||
                  C1.ATTRIBUTE1                     ||CHR(9)||
                  C1.ATTRIBUTE2                     ||CHR(9)||
                  C1.ATTRIBUTE3                     ||CHR(9)||
                  C1.ATTRIBUTE4                     ||CHR(9)||
                  C1.ATTRIBUTE5                     ||CHR(9)||
                  C1.ATTRIBUTE6                     ||CHR(9)||
                  C1.ATTRIBUTE7                     ||CHR(9)||
                  C1.ATTRIBUTE8                     ||CHR(9)||
                  C1.ATTRIBUTE9                     ||CHR(9)||
                  C1.ATTRIBUTE10                    ||CHR(9)||
                  C1.ATTRIBUTE11                    ||CHR(9)||
                  C1.ATTRIBUTE12                    ||CHR(9)||
                  C1.ATTRIBUTE13                    ||CHR(9)||
                  C1.ATTRIBUTE14                    ||CHR(9)||
                  C1.ATTRIBUTE15                    ||CHR(9)||
                  C1.BOM_RESOURCE_ID                ||CHR(9)||
                  C1.REQUEST_ID                     ||CHR(9)||
                  C1.PROGRAM_APPLICATION_ID         ||CHR(9)||
                  C1.PROGRAM_ID                     ||CHR(9)||
                  C1.PROGRAM_UPDATE_DATE            ||CHR(9)||
                  C1.USSGL_TRANSACTION_CODE         ||CHR(9)||
                  C1.GOVERNMENT_CONTEXT             ||CHR(9)||
                  C1.CLOSED_REASON                  ||CHR(9)||
                  C1.CLOSED_DATE                    ||CHR(9)||
                  C1.TRANSACTION_REASON_CODE        ||CHR(9)||
                  C1.QUANTITY_RECEIVED              ||CHR(9)||
                  C1.SOURCE_REQ_LINE_ID             ||CHR(9)||
                  C1.ORG_ID                         ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE1              ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE2              ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE3              ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE4              ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE5              ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE6              ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE7              ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE8              ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE9              ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE10             ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE11             ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE12             ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE13             ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE14             ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE15             ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE16             ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE17             ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE18             ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE19             ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE20             ||CHR(9)||
                  C1.GLOBAL_ATTRIBUTE_CATEGORY      ||CHR(9)||
                  C1.KANBAN_CARD_ID                 ||CHR(9)||
                  C1.CATALOG_TYPE                   ||CHR(9)||
                  C1.CATALOG_SOURCE                 ||CHR(9)||
                  C1.MANUFACTURER_ID                ||CHR(9)||
                  C1.MANUFACTURER_NAME              ||CHR(9)||
                  C1.MANUFACTURER_PART_NUMBER       ||CHR(9)||
                  C1.REQUESTER_EMAIL                ||CHR(9)||
                  C1.REQUESTER_FAX                  ||CHR(9)||
                  C1.REQUESTER_PHONE                ||CHR(9)||
                  C1.UNSPSC_CODE                    ||CHR(9)||
                  C1.OTHER_CATEGORY_CODE            ||CHR(9)||
                  C1.SUPPLIER_DUNS                  ||CHR(9)||
                  C1.TAX_STATUS_INDICATOR           ||CHR(9)||
                  C1.PCARD_FLAG                     ||CHR(9)||
                  C1.NEW_SUPPLIER_FLAG              ||CHR(9)||
                  C1.AUTO_RECEIVE_FLAG              ||CHR(9)||
                  C1.TAX_USER_OVERRIDE_FLAG         ||CHR(9)||
                  C1.TAX_CODE_ID                    ||CHR(9)||
                  C1.NOTE_TO_VENDOR                 ||CHR(9)||
                  C1.OKE_CONTRACT_VERSION_ID        ||CHR(9)||
                  C1.OKE_CONTRACT_HEADER_ID         ||CHR(9)||
                  C1.ITEM_SOURCE_ID                 ||CHR(9)||
                  C1.SUPPLIER_REF_NUMBER            ||CHR(9)||
                  C1.SECONDARY_UNIT_OF_MEASURE      ||CHR(9)||
                  C1.SECONDARY_QUANTITY             ||CHR(9)||
                  C1.PREFERRED_GRADE                ||CHR(9)||
                  C1.SECONDARY_QUANTITY_RECEIVED    ||CHR(9)||
                  C1.SECONDARY_QUANTITY_CANCELLED   ||CHR(9)||
                  C1.VMI_FLAG                       ||CHR(9)||
                  C1.AUCTION_HEADER_ID              ||CHR(9)||
                  C1.AUCTION_DISPLAY_NUMBER         ||CHR(9)||
                  C1.AUCTION_LINE_NUMBER            ||CHR(9)||
                  C1.REQS_IN_POOL_FLAG              ||CHR(9)||
                  C1.BID_NUMBER                     ||CHR(9)||
                  C1.BID_LINE_NUMBER                
			);

			lv_Scc_Count	:=	lv_Scc_Count	+	1;
		END LOOP;
		UTL_FILE.FClose(lv_UTL_File);
	ELSE
		lv_Err_Count	:=	lv_Err_Count + 1;
		lv_Err_Msg		:=	lv_NGO_Name||': No PO Requisition lines for NGO'; /*Change it for proper error message*/
	END IF;

	INSERT INTO CAPCNV_LOG(
		PROG_ID,
		PROCESS_DATE,
		RECORD_PROCESSED,
		RECORD_REJECTED,
		RECORD_EXTRACTED,
		ERR_MSG
	) VALUES (
		lv_Prog_ID,
		SYSDATE,
		lv_Scc_Count,
		lv_Err_Count,
		lv_Scc_Count-lv_Err_Count,
		lv_Err_Msg
	);

	COMMIT;
END;
/

